clear all
set more off , perm
capture log close

********************************************************************************
*********** This file creates a panel for radio petrol price reports ***********
********************************************************************************

		
***************
** Data Load **
***************

// Load
cd "$dta"
use radio_transmitters.dta , clear

// Only keep FM
drop if transmitter_band == "DAB"

// Check whether name and city are unique for each ID
preserve
gen pepe = 1
collapse (mean) pepe , by(city region id)
bysort id: egen pepito = count(pepe)
tab pepito
restore
* --> Uniquely identified by ID.

// Collapse data
collapse transmitter_nr , by(id city region name parent_id)
bysort id: egen pepe = count(id)
tab pepe
drop pepe transmitter_nr
* --> Unique observation per ID.

// Gen identifier
gen pepe = _n

// Save
cd "$dta"
save stations_proc.dta , replace

// Create daily panel structure
cd "$raw\08_Germany_radio"
import excel date.xlsx , clear first

cd "$dta"
save date.dta , replace

// Merge data sets
gen min = 0
gen max = 1000
rangejoin pepe min max using stations_proc.dta

// Check
bysort date: egen pepito = count(min)
tab pepito
drop pepe pepito min max
* --> For every date we have an observation for each radio station.

// Clean data
order parent_id id city region name
sort parent_id id date

format date %tdDD/NN/CCYY

// Save data and erase temp files
cd "$dta"
compress
save interm_station_panel.dta , replace
erase stations_proc.dta
erase date.dta



*************************
** Code Petrol Reports **
*************************

cd "$dta"
use interm_station_panel.dta , clear

// Create variables
cap gen answer = "no"
cap gen answer_required = ""
cap gen petrol_reports = ""

// Recode answer
replace answer = "yes" if name == "afk max"
replace answer = "yes" if name == "Bayern 3"
replace name = "Bayernwelle Suedost" if name == "Bayernwelle SÃ¼dOst" 
replace answer = "yes" if name == "Bayernwelle Suedost"
replace answer = "yes" if name == "Charivari 98.6"
replace answer = "yes" if name == "Donau 3 FM"
replace answer = "yes" if name == "extra-radio 88.0"
replace answer = "yes" if name == "Hitradio antenne 1"
replace answer = "yes" if name == "hr4"
replace answer = "yes" if name == "Radio Arabella"
replace answer = "yes" if name == "Radio Charivari Regensburg"
replace name = "Radio Charivari Wuerzburg" if name == "Radio Charivari WÃ¼rzburg" 
replace answer = "yes" if name == "Radio Charivari Wuerzburg"
replace answer = "yes" if name == "Radio Galaxy 89.8 Weiden / 105.5 Amberg"
replace answer = "yes" if name == "Radio Galaxy Mittelfranken"
replace answer = "yes" if name == "ROCK ANTENNE"

//Recode answer_required
*yes
replace name = "Bayern 2 Suedbayern" if name == "Bayern 2 SÃ¼dbayern"
replace name = "ENERGY Muenchen" if name == "ENERGY MÃ¼nchen"
replace name = "ENERGY Nuernberg" if name == "ENERGY NÃ¼rnberg"
replace name = "hitradio.rt1 suedschwaben" if name == "hitradio.rt1 sÃ¼dschwaben"
replace name = "Jazztime Nuernberg" if name == "Jazztime NÃ¼rnberg"
replace name = "Radio Muenchen" if name == "Radio MÃ¼nchen"


replace answer_required = "yes" if name == "Radio Gong" | name == "95.5 Charivari" | name == "Antenne Bayern" | name=="B5 aktuell" | ///
 name == "Bayern 1 Schwaben" | name == "Bayern 1 Franken" | name == "Bayern 1 Mainfranken" | name == "Bayern 1 Niederbayern / Oberpfalz" | ///
 name == "Bayern 1 Oberbayern" | name == "Bayern 1 Schwaben" | name == "Bayern 2 Suedbayern" | name == "Bayern 2 Nordbayern" | ///
 name == "ENERGY Muenchen" | name == "ENERGY Nuernberg" | name == "gong fm" | name == "Hit-Radio N1" | name == "hitradio.rt1" | ///
 name == "hitradio.rt1 nordschwaben" | name == "hitradio.rt1 suedschwaben" | name == "Ilmwelle Event" | name == "Radio ISW" | ///
 name == "Radio 2Day" | name == "Radio 7" | name == "Radio 8" | name == "Radio Alpenwelle" | name == "Radio AWN" | ///
 name == "Radio Bamberg" | name == "Radio Charivari Rosenheim" | name == "Radio Eins" | name == "Radio Euroherz" | ///
 name == "Radio F" | name == "Radio Fantasy" | name == "Radio Galaxy 107.9 Ingolstadt" | name == "Radio Galaxy 88.1 Kempten" | /// 
 name == "Radio Galaxy Aschaffenburg" | name == "Radio Galaxy 99.8 Landshut" | name == "Radio Galaxy Oberfranken" | ///
 name == "Radio Galaxy 91.7 Passau" | name == "Radio Galaxy 106.6 Rosenheim" | name == "Radio Gong 96.3" | name == "Gong 97.1" | ///
 name == "Radio IN" | name == "Radio Mainwelle" | name == "Radio Oberland" | name == "Radio Plassenburg" | name == "Radio Primaton" | ///
 name == "Radio Primavera" | name == "Radio Ramasuri" | name == "Radio Trausnitz" | name == "STAR FM 107.8/99.0" | name == "TOP FM"| name == "Unser Radio"

 
*no
replace answer_required = "yes" if name == "afk max" | name == "AFN Bavaria-The Eagle" | name == "Radio AREF" | name == "Bayern 3" | ///
 name == "Bayernwelle Suedost" | name == "BR Klassik" | name == "Camillo 92.9" | name == "Charivari 98.6" | name == "CRM 92.4" | ///
 name == "egoFM" | name == "extra-radio 88.0" | name == "Jazztime Nuernberg" | name == "Klassik Radio" | name == "Pray92,9" | ///
 name == "Radio Arabella" | name == "Radio Charivari Wuerzburg" | name == "RADIO FEIERWERK" | name == "Radio Galaxy 89.8 Weiden / 105.5 Amberg" | ///
 name == "Radio Galaxy Mittelfranken" | name == "Radio Horeb" | name == "Radio LORA" | name == "Radio München" | name == "Radio Z" | ///
 name == "ROCK ANTENNE" 
 
 
 
// Radio reports
format date %tdDD/NN/CCYY
replace petrol_reports = "yes" if name == "Radio Galaxy 106.6 Rosenheim" & date >= date("20130601","YMD") & date < date("20170726","YMD")
replace petrol_reports = "yes" if name == "extra-radio 88.0" & date >= date("20140202","YMD") & date < date("20170305","YMD")
replace petrol_reports = "yes" if name == "Radio Charivari Rosenheim" & date >= date("20130601","YMD") & date < date("20170726","YMD")
replace petrol_reports = "yes" if name == "Radio Arabella" & date >= date("20140425","YMD")


cd "$dta"
save radio_stations_daily_panel.dta , replace
erase interm_station_panel.dta
 
 
 
 





